﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;
using NPOI.XSSF.UserModel;

namespace NF_Base
{
    public class ExportClass : IDisposable
    {
        private bool disposed;

        private readonly string fileName;
        private FileStream fs;
        private IWorkbook workbook;

        //文件不存在，1：文件被占用，2：文件未被占用
        private int FileNoExist = 0, FileOccupy = 1, FileNormal = 2; 

        public ExportClass(string fileName)
        {
            this.fileName = fileName;
            disposed = false;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    if (fs != null)
                        fs.Close();
                }
                fs = null;
                disposed = true;
            }
        }

        /// <summary>
        /// 获取表头
        /// </summary>
        /// <param name="data">dvg</param>
        /// <param name="sheetName">sheet名称</param> 
        /// <param name="columnames">不需要的列的名称</param>
        /// <param name="controlColumn">需要删除操作列的个数</param>
        /// <returns>bool</returns>
        public bool GetTableHeader(DataGridView data,string sheetName,int controlColumn=0)
        {
            bool result = false;
            try
            {
                controlColumn = controlColumn + 1;
                fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                    workbook = new XSSFWorkbook();
                else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
                    workbook = new HSSFWorkbook();
                ISheet sheet = null;
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                var headerRow = (HSSFRow) sheet.CreateRow(0);
                var headstyle = (HSSFCellStyle) workbook.CreateCellStyle();
                var font = (HSSFFont) workbook.CreateFont();
                headstyle.Alignment = HorizontalAlignment.CENTER;
                font.Boldweight = 700;
                font.FontName = "微软雅黑";
                font.FontHeightInPoints = 14;
                headstyle.SetFont(font);
                headerRow.RowStyle = headstyle;

                int state = 0;
                for (int j = 0, max = data.Columns.Count - controlColumn; j < max; ++j)
                {
                    if (data.Columns[j].Visible)
                    {
                        headerRow.CreateCell(state).SetCellValue(data.Columns[j].HeaderText);
                        state++;
                    }
                }
                workbook.Write(fs);
                result = true;
            }
            catch (Exception ex)
            {
                ErroLog.SysLog(ex);
                result = false;
            }
            return result;
        }


        #region 检测文件被占用

        [DllImport("kernel32.dll")]
        public static extern IntPtr _lopen(string lpPathName, int iReadWrite);

        [DllImport("kernel32.dll")]
        public static extern bool CloseHandle(IntPtr hObject);

        public const int OF_READWRITE = 2;
        public const int OF_SHARE_DENY_NONE = 0x40;
        public readonly IntPtr HFILE_ERROR = new IntPtr(-1);

        /// <summary>
        /// 检测文件被占用
        /// </summary>
        /// <param name="FileNames">要检测的文件路径</param>
        /// <returns>0:文件不存在，1：文件被占用，2：文件未被占用</returns>
        public int CheckFiles(string filenames)
        {
            if (!File.Exists(filenames))
            {
                //文件不存在
                return FileNoExist;
            }
            IntPtr vHandle = _lopen(filenames, OF_READWRITE | OF_SHARE_DENY_NONE);
            if (vHandle == HFILE_ERROR)
            {
                //文件被占用
                return FileOccupy;
            }
            //文件没被占用
            if (CloseHandle(vHandle))
            {
                return FileNormal;  
            }
            return FileNormal;
            
        }

        #endregion



        #region 表格导入excel

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public int DataGridViewToExcel(DataGridView data, string sheetName, bool isColumnWritten, int controlColumn)
        {
            controlColumn = controlColumn + 1;
            List<string> listname = new List<string>();
            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
                workbook = new HSSFWorkbook();
            try
            {
                ISheet sheet = null;
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }
                var count = 0;
                if (isColumnWritten) //写入DataTable的列名
                {
                    //设置字体，大小，对齐方式
                    var headerRow = (HSSFRow) sheet.CreateRow(0);
                    var headstyle = (HSSFCellStyle) workbook.CreateCellStyle();
                    var font = (HSSFFont) workbook.CreateFont();
                    headstyle.Alignment = HorizontalAlignment.CENTER;
                    font.Boldweight = 700;
                    font.FontName = "微软雅黑";
                    font.FontHeightInPoints = 14;
                    headstyle.SetFont(font);
                    headerRow.RowStyle = headstyle;
                    int state = 0;
                    //导出表头
                    for (int j = 0, max = data.Columns.Count - controlColumn; j < max; j++)
                    {
                        if (data.Columns[j].Visible)
                        {
                            headerRow.CreateCell(state).SetCellValue(data.Columns[j].HeaderText);
                            listname.Add(data.Columns[j].Name);
                            state++;
                        }
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }
                //导出数据
                for (int i = 0, maxs = data.Rows.Count; i < maxs; i++)
                {
                    int j = 0;
                    IRow row= sheet.CreateRow(count);

                    foreach (string name in listname)
                    {
                        var value = data.Rows[i].Cells[name].Value;
                        row.CreateCell(j).SetCellValue(value.ToString());
                        j++;
                    }
                    count++;
                }
                workbook.Write(fs); //写入到excel
                return count-1;
            }
            catch (Exception ex)
            {
                ErroLog.SysLog(ex);
                return -1;
            }
        }

        #endregion

        #region excel导入表格

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        ///<param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataGridView ExcelToDataGridView(bool isFirstRowColumn,string sheetName)
        {
            ISheet sheet = null;
            var data = new DataGridView();
            var startRow = 0;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    var firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            var cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                var cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    var column = new DataGridViewTextBoxColumn();
                                    column.HeaderText = cellValue;
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    var rowCount = sheet.LastRowNum;
                    for (var i = startRow; i <= rowCount; ++i)
                    {
                        var row = sheet.GetRow(i);
                        if (row.GetCell(1) != null && row.GetCell(2) != null && 
                            row.GetCell(3) != null && row.GetCell(4) != null && 
                            row.GetCell(5) != null && row.GetCell(6) != null && 
                            row.GetCell(7) != null) 
                        {
                            //没有数据的行默认是null　　　　　　　
                            var datarow = new DataGridViewRow();
                            datarow.CreateCells(data);
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                {
                                    var ss = row.GetCell(j);
                                    datarow.Cells[j].Value = row.GetCell(j).ToString();
                                }
                            }
                            data.Rows.Add(datarow);  
                        }
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                ErroLog.SysLog(ex);
                return data;
            }
        }

        #endregion

        #region 调用导出方法

        //     try
        //{
        //    string saveFileName = "";
        //    SaveFileDialog saveDialog = new SaveFileDialog();
        //    saveDialog.DefaultExt = "xls";
        //    saveDialog.Filter = "Excel文件|*.xls";
        //    saveDialog.FileName = fileName;
        //    if (saveDialog.ShowDialog() == DialogResult.OK)
        //    {
        //        saveFileName = saveDialog.FileName;
        //        using (ExcelHelper excelHelper = new ExcelHelper(saveFileName))
        //        {
        //            int count = excelHelper.DataTableToExcel(dvglist, "MySheet", true);
        //        }
        //    }
        //}
        //catch (Exception ex)
        //{

        //} 

        #endregion

        #region 调用导入方法，追加到表格后面

        //    try
        //{
        //    OpenFileDialog opendialog = new OpenFileDialog();
        //    opendialog.DefaultExt = "xls";
        //    opendialog.Filter = "Excel文件|*.xls";

        //    if (opendialog.ShowDialog() == DialogResult.OK)
        //    {
        //        string file = opendialog.FileName;
        //        using (ExcelHelper excelHelper = new ExcelHelper(file))
        //        {
        //            excelHelper.ExcelToDataTable("MySheet", true, dvglist);

        //        }
        //    }
        //}
        //catch (Exception ex)
        //{
        //    Console.WriteLine("Exception: " + ex.Message);
        //} 

        #endregion
    }
}